<?php
	require_once './Tool/DB/SqlDBManager.class.php';
	require_once './Tool/Common/PrepareInput.php';
	
	//Create DBManager Object
	$sqlDBManager = new SqlDBManager();
	
	if (!empty($_SESSION['userType'])) {
		$userType = $_SESSION['userType'];
	}
	if(!empty($_POST['ddlCategory'])){
		$category = prepareInput($_POST['ddlCategory']);
	}
	if(!empty($_POST['date'])){
		$date = date('Y-m-d', strtotime(prepareInput($_POST['date'])));
		$month = date('F', strtotime(prepareInput($_POST['date'])));
		$year = date('Y', strtotime(prepareInput($_POST['date'])));
	}

	//Validate input
    if(!preg_match("/^[A-Za-z]*$/",$category)){
        echo "<font color=red>Invalid Category Type.</font><br/>";
        exit();
    }
    if(!preg_match("/^[0-9]{4}-(0[1-9]|1[0-2])-(0[1-9]|[1-2][0-9]|3[0-1])$/",$date)){
        echo "<font color=red>Invalid Date Format.</font><br/>";
        exit();
    }
	
	if (!empty($_GET['queryString'])){
		$queryString=$_GET['queryString'];
		$nricList = $_SESSION[$queryString];
	}

	$_SESSION["date"] = $date;
	$_SESSION["category"] = $category;

	//Export button
	if ($userType != "Therapy Assistant") {
		echo"<form action=\"./IndividualRpts/export.php?queryString=".$queryString."\" method=\"post\">
				<input type=\"submit\" button class=\"btn\" id=\"btnExport\"value=\"Export\"/>
				
			</form>";
	}
	//Category == All --> has physical && social both
	if($category=="All"){
		//draw table outline
		echo "<div class='tablereport' id='table'style='width:auto;overflow:scroll'>
			<table id = 'tableExcel' cellspacing='1' cellpadding='1' align='center' name='tableIndividual' style='margin:0 20px' border='1'>
				<tr>
					<td>S/No</td>
					<td>NRIC</td>
					<td>Name</td>
					<td colspan='2'>Total No.of Activities Conducted in ".$month." / ".$year."</td>
					<td colspan='2'>Total Attendance</td>
					<td colspan='2'>Overall Performance</td>
				</tr>";
		$sno = 1;
		for($i=0;$i<count($nricList);$i++){	
			$NRIC=$nricList[$i];

			//first row	need set the column heading 
			if($i==0){
				//input data	
				$sql = "SELECT * from Client where NRIC=?";
				$parameters1=array($NRIC);
				$res1=$sqlDBManager->queryRow($sql,$parameters1);
				
				//client info
				if(!empty($res1)){
					echo"<tr>
							<td rowspan='2'>
							" . $sno . "</td>
							<td rowspan='2'>
							" . $res1['NRIC']. "</td>
							<td rowspan='2'>
							" . $res1['CustomerName']. "</td>
							<td>Physical Activities</td>
							<td>Social Activities</td>";
				}
		
				$sql="SELECT COUNT( * ) FROM  attendancerecord att,  client cl WHERE cl.NRIC = ? AND cl.NRIC=att.NRIC AND MONTHNAME( att.SignOutTimeStamp ) =  ? AND YEAR( att.SignOutTimeStamp ) =  ? ;";
				$parameters4=array($NRIC,$month,$year);
				//total attendance	
				$res4=$sqlDBManager->queryRow($sql,$parameters4);
				if(!empty($res4[0])){
					echo"<td colspan='2' rowspan='2'>" . $res4[0]. "</td>";
				}
				else{
					echo"<td colspan='2'rowspan='2'>0</td>";
				}
		
				echo "<td>Physical Activities</td>
					<td>Social Activities</td>
					</tr>";
				
				$sql="SELECT COUNT( * ) FROM  activityrecord acr,  client cl,  activity act WHERE cl.NRIC =  ? AND acr.NRIC =  cl.NRIC AND act.activityId = acr.activityId AND act.Category = ? AND MONTHNAME( acr.Date ) =  ? AND YEAR( acr.Date ) =  ? ;";
				$parameters2=array($NRIC,'Physical',$month,$year);
				$parameters3=array($NRIC,'Social',$month,$year);
				
				//total no of physical activities
				$res2=$sqlDBManager->queryRow($sql,$parameters2);
				
				//total no of social activities
				$res3=$sqlDBManager->queryRow($sql,$parameters3);
				
				//input data into physical column
				if(!empty($res2[0])){
					echo"<td>" . $res2[0] . "</td>";
				}
				else{
					echo"<td>0</td>";
				}
				//input data into social column
				if(!empty($res3[0])){
					echo"<td>" . $res3[0]. "</td>";
				}else{
					echo"<td>0</td>";
				}
				
				$sql="SELECT SUM(acr.Performance)/Count(*) FROM  activityrecord acr,  client cl,  activity act WHERE cl.NRIC =  ? AND acr.NRIC =  cl.NRIC AND act.activityId = acr.activityId AND act.Category =  ? AND MONTHNAME( acr.Date ) =  ? AND YEAR( acr.Date ) =  ? ;";
				$parameters5=array($NRIC,'Physical',$month,$year);
				$parameters6=array($NRIC,'Social',$month,$year);
				
				//overall performance of physical activities
				$res5=$sqlDBManager->queryRow($sql,$parameters5);
				
				//overall performance of social activities
				$res6=$sqlDBManager->queryRow($sql,$parameters6);
				
				//input data into physical column
				if(!empty($res5[0])){
					echo"<td>" . $res5[0]. "</td>";
				}
				else{
					echo"<td>0</td>";
				}
				//input data into social column
				if(!empty($res6[0])){
					echo"<td>" . $res6[0]. "</td>";
				}else{
					echo"<td>0</td>";
				}		
				echo"</tr>";
			}else{
				//input data
				$sql = "SELECT * from Client where NRIC=?";
				$parameters1=array($NRIC);
				$res1=$sqlDBManager->queryRow($sql,$parameters1);
				
				//client info
				if(!empty($res1)){
					echo"<tr>
							<td>
							" . $sno . "</td>
							<td>
							" . $res1['NRIC']. "</td>
							<td>
							" . $res1['CustomerName']. "</td>";
				}
							
				$sql="SELECT COUNT( * ) FROM  activityrecord acr,  client cl,  activity act WHERE cl.NRIC =  ? AND acr.NRIC =  cl.NRIC AND act.activityId = acr.activityId AND act.Category = ? AND MONTHNAME( acr.Date ) =  ? AND YEAR( acr.Date ) =  ? ;";
				$parameters2=array($NRIC,'Physical',$month,$year);
				$parameters3=array($NRIC,'Social',$month,$year);
				
				//total no of physical activities
				$res2=$sqlDBManager->queryRow($sql,$parameters2);
				
				//total no of social activities
				$res3=$sqlDBManager->queryRow($sql,$parameters3);
				
				//input data into physical column
				if(!empty($res2[0])){
					echo"<td>" . $res2[0] . "</td>";
				}
				else{
					echo"<td>0</td>";
				}
				//input data into social column
				if(!empty($res3[0])){
					echo"<td>" . $res3[0]. "</td>";
				}else{
					echo"<td>0</td>";
				}
				
				$sql="SELECT COUNT( * ) FROM  attendancerecord att,  client cl WHERE cl.NRIC = ? AND cl.NRIC=att.NRIC AND MONTHNAME( att.SignOutTimeStamp ) =  ? AND YEAR( att.SignOutTimeStamp ) =  ? ;";
				$parameters4=array($NRIC,$month,$year);
				//total attendance
				$res4=$sqlDBManager->queryRow($sql,$parameters4);
				if(!empty($res4[0])){
					echo"<td colspan='2'>" . $res4[0]. "</td>";
				}
				else{
					echo"<td colspan='2'>0</td>";
				}
				
				$sql="SELECT SUM(acr.Performance)/Count(*) FROM  activityrecord acr,  client cl,  activity act WHERE cl.NRIC =  ? AND acr.NRIC =  cl.NRIC AND act.activityId = acr.activityId AND act.Category =  ? AND MONTHNAME( acr.Date ) =  ? AND YEAR( acr.Date ) =  ? ;";
				$parameters5=array($NRIC,'Physical',$month,$year);
				$parameters6=array($NRIC,'Social',$month,$year);
				
				//overall performance of physical activities
				$res5=$sqlDBManager->queryRow($sql,$parameters5);
				
				//overall performance of social activities
				$res6=$sqlDBManager->queryRow($sql,$parameters6);
				
				//input data into physical column
				if(!empty($res5[0])){
					echo"<td>" . $res5[0]. "</td>";
				}
				else{
					echo"<td>0</td>";
				}
				//input data into social column
				if(!empty($res6[0])){
					echo"<td>" . $res6[0]. "</td>";
				}else{
					echo"<td>0</td>";
				}
				echo"</tr>";
			}
			$sno++;
		}
		//End of table
		echo "</table>
				</div>
				</br>
				</br>";
	}//Physical or Social
	else{
		//draw table outline
		echo "<div class='tablereport' id='table'style='width:auto;overflow:scroll;'>
			<table id = 'tableExcel' cellspacing='2' cellpadding='2' align='center' name='tableIndividual' style='margin:0 20px' border='1'>
				<tr>
					<td>S/No</td>
					<td>NRIC</td>
					<td>Name</td>
					<td colspan='2'>Total No.of Activities Conducted in ".$month." / ".$year."</td>
					<td colspan='2'>Total Attendance</td>
					<td colspan='2'>Overall Performance</td>
				</tr>";
		$sno = 1;
		for($i=0;$i<count($nricList);$i++){
			$NRIC=$nricList[$i];
			//first row need set the column headings
			if($i==0){
				//input data
				$sql = "SELECT * from Client where NRIC=?";
				$parameters1=array($NRIC);
				$res1=$sqlDBManager->queryRow($sql,$parameters1);	

				//client info		
				if(!empty($res1)){
					echo"<tr>
					<td rowspan='2'>
					" . $sno . "</td>
					<td rowspan='2'>
					" . $res1['NRIC']. "</td>
					<td rowspan='2'>
					" . $res1['CustomerName']. "</td>
					<td colspan='2'>".$category." Activities</td>";
				}
				
				$sql="SELECT COUNT( * ) FROM  attendancerecord att,  client cl WHERE cl.NRIC = ? AND cl.NRIC=att.NRIC AND MONTHNAME( att.SignOutTimeStamp ) =  ? AND YEAR( att.SignOutTimeStamp ) =  ? ;";
				$parameters3=array($NRIC,$month,$year);
				
				//total attendance
				$res3=$sqlDBManager->queryRow($sql,$parameters3);
				if(!empty($res3[0])){
					echo"<td colspan='2' rowspan='2'>" . $res3[0]. "</td>";
				}
				else{
					echo"<td colspan='2' rowspan='2'>0</td>";
				}
				
				echo "<td colspan='2'>".$category." Activities</td></tr>";
				
				$sql="SELECT COUNT( * ) FROM  activityrecord acr,  client cl,  activity act WHERE cl.NRIC =  ? AND acr.NRIC =  cl.NRIC AND act.activityId = acr.activityId AND act.Category = ? AND MONTHNAME( acr.Date ) =  ? AND YEAR( acr.Date ) =  ? ;";
				$parameters2=array($NRIC,$category,$month,$year);
				
				//total no of category selected activities
				$res2=$sqlDBManager->queryRow($sql,$parameters2);
				
				//input data into category column
				if(!empty($res2[0])){
					echo"<td colspan='2'>" . $res2[0]. "</td>";
				}
				else{
					echo"<td colspan='2'>0</td>";
				}
				
				
				$sql="SELECT SUM(acr.Performance)/Count(*) FROM  activityrecord acr,  client cl,  activity act WHERE cl.NRIC =  ? AND acr.NRIC =  cl.NRIC AND act.activityId = acr.activityId AND act.Category =  ? AND MONTHNAME( acr.Date ) =  ? AND YEAR( acr.Date ) =  ? ;";
				$parameters4=array($NRIC,$category,$month,$year);
				
				//overall performance of category activities
				$res4=$sqlDBManager->queryRow($sql,$parameters4);
				
				//input data into category column
				if(!empty($res4[0])){
					echo"<td colspan='2'>" . $res4[0]. "</td>";
				}
				else{
					echo"<td colspan='2'>0</td>";
				}
				echo "<tr/>";
			}else{
				//input data
				$sql = "SELECT * from Client where NRIC=?";
				$parameters1=array($NRIC);
				$res1=$sqlDBManager->queryRow($sql,$parameters1);
				
				//client info
				if(!empty($res1)){
					echo"<tr>
					<td>
					" . $sno . "</td>
					<td>
					" . $res1['NRIC']. "</td>
					<td>
					" . $res1['CustomerName']. "</td>";
				}
			
				$sql="SELECT COUNT( * ) FROM  activityrecord acr,  client cl,  activity act WHERE cl.NRIC =  ? AND acr.NRIC =  cl.NRIC AND act.activityId = acr.activityId AND act.Category = ? AND MONTHNAME( acr.Date ) =  ? AND YEAR( acr.Date ) =  ? ;";
				$parameters2=array($NRIC,$category,$month,$year);
				
				//total no of category selected activities
				$res2=$sqlDBManager->queryRow($sql,$parameters2);
				
				//input data into category column
				if(!empty($res2[0])){
					echo"<td colspan='2'>" . $res2[0]. "</td>";
				}
				else{
					echo"<td colspan='2'>0</td>";
				}
				
				$sql="SELECT COUNT( * ) FROM  attendancerecord att,  client cl WHERE cl.NRIC = ? AND cl.NRIC=att.NRIC AND MONTHNAME( att.SignOutTimeStamp ) =  ? AND YEAR( att.SignOutTimeStamp ) =  ? ;";
				$parameters3=array($NRIC,$month,$year);
				
				//total attendance
				$res3=$sqlDBManager->queryRow($sql,$parameters3);
				if(!empty($res3[0])){
					echo"<td colspan='2'>" . $res3[0]. "</td>";
				}
				else{
					echo"<td colspan='2'>0</td>";
				}
				
				$sql="SELECT SUM(acr.Performance)/Count(*) FROM  activityrecord acr,  client cl,  activity act WHERE cl.NRIC =  ? AND acr.NRIC =  cl.NRIC AND act.activityId = acr.activityId AND act.Category =  ? AND MONTHNAME( acr.Date ) =  ? AND YEAR( acr.Date ) =  ? ;";
				$parameters4=array($NRIC,$category,$month,$year);
				
				//overall performance of category activities
				$res4=$sqlDBManager->queryRow($sql,$parameters4);
				
				//input data into category column
				if(!empty($res4[0])){
					echo"<td colspan='2'>" . $res4[0]. "</td>";
				}
				else{
					echo"<td colspan='2'>0</td>";
				}
				echo "<tr/>";
			}
			$sno++;
		}
		echo"</table>
			</div>
			</br>
			</br>";
	}
	//close connection
	$sqlDBManager->close_connect();
?>